if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spFdsDNUpdateStudyStaff]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spFdsDNUpdateStudyStaff]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

/***************************************************************************
* Name:			spFdsDNUpdateStudyStaff
* Purpose:		Using table FdsProtocolStagingHumanProtocols, update Staffing tables
*
* PARAMETERS
* Name				Description					
* -------------		-----------------------------------------------------------
* n/a
*
* ERRORS USED		Description
* -------------		-----------------------------------------------------
* n/a
*
* RETURN VALUE
* Value    			Description					
* --------------	------------------------------------------------------
* @_exitStatus		An exit status of either true or false
***************************************************************************/

CREATE PROCEDURE dbo.spFdsDNUpdateStudyStaff
AS
BEGIN

BEGIN TRANSACTION spFdsDNUpdateStudyStaff

	DECLARE	@SUCCESS	AS BIT
	DECLARE	@FAILURE	AS BIT
	DECLARE @_subsprocStatus	AS BIT
	DECLARE @_UPDATE AS BIT

	SET	@SUCCESS = 0
	SET	@FAILURE = 1
	SET @_UPDATE = 1


UPDATE SvcEntity_AdmStaffedPerson_Map
SET AdmPersonId = psc.AdmPersonId, 
    AdmStaffedPersonTypeId = psc.AdmStaffedPersonTypeId
FROM SvcEntity_AdmStaffedPerson_Map sam
INNER JOIN FdsProtocolStagingHumProtStaff psc
ON sam.Id = psc.SvcEntityStaffedMapId
WHERE psc.IsUpdate = @_UPDATE 

 IF @@ERROR > 0 GOTO ERROR_HANDLER 


---Update the organizatonId if necessary
UPDATE PrtStaffedPerson
SET FwkOrganizationId = FwkPrimaryOrgId
FROM PrtStaffedPerson psp
INNER JOIN FdsProtocolStagingHumProtStaff psc
ON psp.svcEntityStaffedPersonMapId = psc.SvcEntityStaffedMapId
WHERE psc.IsUpdate = @_UPDATE 

 IF @@ERROR > 0 GOTO ERROR_HANDLER


--- Create Contacts for records that have isContact (bGetsMail) set to 1. 
	Insert into SvcEntity_AdmContact_Map(SvcEntityId, AdmPersonId, AdmContactTypeId, DateCreated)
	SELECT svcEntityId, AdmPersonId, 5 as AdmContactTypeId, getDate() as DateCreated
	FROM  FdsProtocolStagingHumProtStaff psc
	WHERE bGetsMail = 1
 	AND NOT EXISTS ( SELECT 1 FROM SvcEntity_AdmContact_Map 
				  WHERE SvcEntityId = psc.SvcEntityId
				    AND AdmPersonId = psc.AdmPersonId
				)
	AND psc.IsUpdate = @_UPDATE 

	IF @@ERROR > 0 GOTO ERROR_HANDLER

	-- Remove contacts that have their bGetsmail set to 0
	DELETE SvcEntity_AdmContact_Map 
	FROM SvcEntity_AdmContact_Map sam
	INNER JOIN FdsProtocolStagingHumProtStaff psc
	ON sam.SvcEntityId = psc.SvcEntityId 
	 AND sam.AdmPersonId = psc.AdmPersonId 
	WHERE psc.bGetsMail = 0
	AND psc.IsUpdate = @_UPDATE 
		
	IF @@ERROR > 0 GOTO ERROR_HANDLER

--- Grant access to the study staff that have not been granted rights
  		
	DECLARE @_READ_ACCESS_BITMASK AS INTEGER
	DECLARE @_EXPLICIT_PROVISION_BITVALUE AS INTEGER
	
		
	SET @_EXPLICIT_PROVISION_BITVALUE = 2

	SET @_READ_ACCESS_BITMASK = (SELECT dbo.fnGetAccessRightBitvalueByName ('Read'))
		

	DECLARE curStudyStaffAccessRights CURSOR LOCAL FAST_FORWARD FOR
	SELECT pse.FwkPrincipalId, pp.FwkResourceId
    	FROM FdsProtocolStagingHumProtStaff pse
	INNER JOIN PrtProtocol pp
	ON pp.Id = pse.PrtProtocolId 
	WHERE pse.FwkPrincipalId is not null 
	AND pse.IsUpdate = @_UPDATE
    	AND NOT EXISTS ( SELECT 1
					 FROM FwkResource_DomainPrincipal_Map fdm
					 WHERE pse.FwkPrincipalId = fdm.FwkDomainPrincipalId
					  AND pp.FwkResourceId = fdm.FwkResourceId
					)
	AND pse.IsUpdate = @_UPDATE

	DECLARE @_resourceId AS INTEGER
	DECLARE @_principalId AS INTEGER

	OPEN curStudyStaffAccessRights 
	FETCH NEXT FROM curStudyStaffAccessRights INTO @_principalId ,  @_resourceId

	WHILE @@FETCH_STATUS = 0
	BEGIN

		EXEC @_subsprocStatus = spFwkNGrantAccess  @_principalId ,  @_resourceId,
					  @_EXPLICIT_PROVISION_BITVALUE, @_READ_ACCESS_BITMASK

		
		IF @_subsprocStatus = @FAILURE GOTO ERROR_HANDLER

		FETCH NEXT FROM curStudyStaffAccessRights INTO @_principalId,  @_resourceId
		
	END

	
	-- close and deallocate cursor
	CLOSE curStudyStaffAccessRights
	DEALLOCATE curStudyStaffAccessRights 


SET NOCOUNT OFF
COMMIT TRANSACTION  spFdsDNUpdateStudyStaff
RETURN @SUCCESS

ERROR_HANDLER: 
  SET NOCOUNT OFF
  ROLLBACK TRANSACTION spFdsDNUpdateStudyStaff
 RETURN @FAILURE

END

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

